import pymysql
from flask import Flask, request, jsonify, send_from_directory
import os
import uuid
from datetime import datetime

from flask_cors import CORS
app = Flask(__name__)
# 为整个应用开启跨域支持，指定允许的源
CORS(app, origins=["*"])

# 本地文件存储的基础目录
localPathDir = 'uploads'


def connect_database():
  try:
    # 建立数据库连接，并指定游标类型为 DictCursor
    conn = pymysql.connect(
      host="localhost",
      port=3306,
      user="root",
      password="123456",
      database="flask_fish_map",
      charset='utf8mb4',
      cursorclass=pymysql.cursors.DictCursor
    )
    return conn
  except pymysql.Error as e:
    print(f"数据库连接错误: {e}")
    return None


# 登录接口，指定请求方法为 POST
@app.route('/login', methods=['POST'])
def login():
  try:
    data = request.get_json()
    if not data:
      return jsonify({'message': '请求数据格式错误'}), 400
    username = data.get('username')
    password = data.get('password')
    if username is None:
      app.logger.warning('请输入用户名')
      return jsonify({'message': '请输入用户名'}), 400
    if password is None:
      app.logger.warning('请输入密码')
      return jsonify({'message': '请输入密码'}), 400
    conn = connect_database()
    if conn is None:
      app.logger.warning('数据库连接失败')
      return jsonify({'message': '数据库连接失败'}), 500
    cursor = conn.cursor()
    try:
      app.logger.warning('数据库连接成功')
      # 查询用户信息，注意参数以元组形式传入
      cursor.execute('SELECT * FROM users WHERE username = %s', (username,))
      user = cursor.fetchone()
      if user:
        if user['password'] == password:
          return jsonify({'message': '登录成功', "data": user}), 200
        else:
          return jsonify({'message': '账号或密码错误'}), 401
      else:
        return jsonify({'message': '用户不存在'}), 404
    except pymysql.Error as e:
      app.logger.error(f'数据库查询错误: {e}')
      return jsonify({'message': f'数据库查询错误: {e}'}), 500
    finally:
      cursor.close()
      conn.close()
  except Exception as e:
    app.logger.error(f'请求处理出错: {e}')
    return jsonify({'message': f'请求处理出错: {e}'}), 500


@app.route('/checkUsername', methods=['POST'])
def checkUsername():
  try:
    data = request.get_json()
    if not data:
      return jsonify({'message': '请求数据格式错误'}), 400
    username = data.get('username')
    if username is None:
      app.logger.warning('请输入用户名')
      return jsonify({'message': '请输入用户名'}), 400
    conn = connect_database()
    if conn is None:
      app.logger.warning('数据库连接失败')
      return jsonify({'message': '数据库连接失败'}), 500
    cursor = conn.cursor()
    try:
      app.logger.warning('数据库连接成功')
      # 查询用户信息，注意参数以元组形式传入
      cursor.execute('SELECT * FROM users WHERE username = %s', (username,))
      user = cursor.fetchone()
      if user:
        return jsonify({'message': '该账号已被注册'}), 201
      else:
        return jsonify({'message': '该账号未被注册'}), 200
    except pymysql.Error as e:
      app.logger.error(f'数据库查询错误: {e}')
      return jsonify({'message': f'数据库查询错误: {e}'}), 500
    finally:
      cursor.close()
      conn.close()
  except Exception as e:
    app.logger.error(f'请求处理出错: {e}')
    return jsonify({'message': f'请求处理出错: {e}'}), 500


@app.route('/register', methods=['POST'])
def register():
  try:
    data = request.get_json()
    if not data:
      return jsonify({'message': '请求数据格式错误'}), 400
    nickName = data.get('nickName')
    username = data.get('username')
    password = data.get('password')
    if nickName is None:
      app.logger.warning('请输入用户名')
      return jsonify({'message': '请输入用户名'}), 400
    if username is None:
      app.logger.warning('请输入账号')
      return jsonify({'message': '请输入账号'}), 400
    if password is None:
      app.logger.warning('请输入密码')
      return jsonify({'message': '请输入密码'}), 400
    conn = connect_database()
    if conn is None:
      app.logger.warning('数据库连接失败')
      return jsonify({'message': '数据库连接失败'}), 500
    cursor = conn.cursor()
    try:
      app.logger.warning('数据库连接成功')
      # 查询用户信息，注意参数以元组形式传入
      cursor.execute('INSERT INTO users (nickName, username, password) VALUES (%s, %s, %s)',
                     (nickName, username, password))
      conn.commit()
      cursor.execute('SELECT * FROM users WHERE username = %s', (username,))
      user = cursor.fetchone()
      if user:
        return jsonify({'message': f'注册成功'}), 200
      else:
        return jsonify({'message': '注册失败'}), 500
    except pymysql.Error as e:
      app.logger.error(f'数据库查询错误: {e}')
      return jsonify({'message': f'数据库查询错误: {e}'}), 500
    finally:
      cursor.close()
      conn.close()
  except Exception as e:
    app.logger.error(f'请求处理出错: {e}')
    return jsonify({'message': f'请求处理出错: {e}'}), 500


@app.route('/getDisscuss', methods=['GET'])
def getDisscuss():
  try:
    page_num = request.args.get('pageNum', default=1, type=int)
    page_size = request.args.get('pageSize', default=10, type=int)
    offset = (page_num - 1) * page_size

    conn = connect_database()
    if conn is None:
      app.logger.warning('数据库连接失败')
      return jsonify({'message': '数据库连接失败'}), 500
    cursor = conn.cursor()
    try:
      app.logger.warning('数据库连接成功')
      cursor.execute('SELECT * FROM posts LIMIT %s OFFSET %s', (page_size, offset))
      posts = cursor.fetchall()
      for post in posts:
        userId = post['userId']
        cursor.execute('SELECT id, nickName FROM users WHERE id = %s', (userId))
        post['user'] = cursor.fetchone()
      cursor.execute('SELECT count(*) total FROM posts')
      total = cursor.fetchone()['total']
      return jsonify({'message': '查询成功', 'data': {
        'data': posts,
        'total': total,
      }}), 200
    except pymysql.Error as e:
      app.logger.error(f'数据库查询错误: {e}')
      return jsonify({'message': f'数据库查询错误: {e}'}), 500
    finally:
      cursor.close()
      conn.close()
  except Exception as e:
    app.logger.error(f'请求处理出错: {e}')
    return jsonify({'message': f'请求处理出错: {e}'}), 500

@app.route('/addDisscuss', methods=['POST'])
def addDisscuss():
    data = request.get_json()
    if not data:
      return jsonify({'message': '请求数据格式错误'}), 400
    userId = data.get('userId')
    title = data.get('title')
    img = data.get('img')
    reply = data.get('reply')
    fishType = data.get('fishType')
    diaogan = data.get('diaogan')
    yuer = data.get('yuer')

    if userId is None:
      return jsonify({'message': '请输入用户名'}), 400
    conn = connect_database()
    if conn is None:
      app.logger.warning('数据库连接失败')
      return jsonify({'message': '数据库连接失败'}), 500
    cursor = conn.cursor()
    # 获取当前时间
    now = datetime.now().strftime("%Y-%m-%d %H:%M")
    try:
      app.logger.warning('数据库连接成功')
      cursor.execute('INSERT INTO posts (userId, title, img, reply, create_time, fishType, diaogan, yuer) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)',
                     (userId, title, img, reply, now, fishType, diaogan, yuer))
      conn.commit()
      return jsonify({'message': '发布成功'}), 200
    except pymysql.Error as e:
      app.logger.error(f'数据库查询错误: {e}')
      return jsonify({'message': f'数据库查询错误: {e}'}), 500
    finally:
      cursor.close()
      conn.close()

@app.route('/file/upload', methods=['POST'])
def upload_file():
  try:
    if 'file' not in request.files:
      return jsonify({'message': '没有文件部分'}), 400
    file = request.files['file']
    if file.filename == '':
      return jsonify({'message': '没有选择文件'}), 400
    if file:
      # 获取原始文件名
      file_name = file.filename
      # 按日期生成路径
      date_path = datetime.now().strftime("/%Y/%m/%d/")
      local_dir = localPathDir + date_path
      # 检查目录是否存在，不存在则创建
      if not os.path.exists(local_dir):
        os.makedirs(local_dir)
      # 生成 UUID
      unique_id = str(uuid.uuid4()).replace("-", "")
      # 获取文件扩展名
      file_extension = os.path.splitext(file_name)[1]
      # 生成真实文件名
      real_file_name = unique_id + file_extension
      # 完整文件路径
      file_path_all = local_dir + real_file_name
      # 保存文件
      file.save(file_path_all)
      # 虚拟路径
      virtual_path = date_path + real_file_name
      return jsonify({'message': '文件上传成功', 'virtualPath': virtual_path, 'realFileName': real_file_name}), 200
  except Exception as e:
    app.logger.error(f'文件上传错误: {e}')
    return jsonify({'message': f'文件上传错误: {e}'}), 500

@app.route('/download/<path:filename>')
def download_file(filename):
    root_dir = os.getcwd()+'/'+localPathDir+'/'
    file_path = os.path.join(root_dir, filename)
    if os.path.exists(file_path):
        return send_from_directory(root_dir, filename)
    else:
        return "File not found", 404

if __name__ == '__main__':
  app.run(host='0.0.0.0', port=5000, debug=True)
